const sql = require('../sql/sqlConfig');
const express = require('express');
let router = express.Router();
const pagination = require('../util/page');

//查商品类型
router.get('/type', function (req, res, next) {
    let option = "select * from `product_type` where `uping` = 1";
    sql.query(option, function (err, rows) {
        if (err) return res.send({errCode:-9999,message:'查询失败'+err})
        res.send({errCode:0,message:'查询产品类型成功',result:rows});
    })
});
//增商品类型
router.post('/typeAdd', function (req, res) {
    let name = req.body.name;
    let pic = req.body.pic;
    let uping = req.body.uping;
    let option = "insert into `product_type`(`name`,`pic`,`uping`) values('" + name + "','" + pic + "','" + uping + "')";
    sql.query(option, function (err, rows) {
        if (err) return res.send({errCode:-9999,message:'新增商品类型失败'+err});
        res.send({errCode:0,message:'新增商品类型成功'});
    })
});
//改商品类型
router.post('/typeAlter', function (req, res) {
    let id = req.body.id;
    let name = req.body.name;
    let pic = req.body.pic;
    let option = "update `product_type` set `name`='" + name + "',`pic`='" + pic + "' where `id`=" + id;
    sql.query(option, function (err, rows) {
        if (err)
            return res.send({errCode:-9999,message:'修改商品类型失败'+err});
        res.send({errCode:0,message:'修改商品类型成功'});
    })
});
//删商品类型
router.post('/typeRemove', function (req, res) {
    let id = req.body.id;
    let option = "select `id` from `product` where `type_id`="+id;
    let option1 = "UPDATE `product_type` SET `uping` = 0 WHERE `id` = "+id;
    sql.query(option, function (err, rows) {
        if (err) return res.send({errCode:-9999,message:'删除商品类型失败'+err});
        if(rows.length>0) return res.send({errCode:9,message:"该类型下存在商品，不可删除"});
        sql.query(option1, function (err, rows) {
            if (err) return res.send({errCode:-9999,message:'删除商品类型失败'+err});
            res.send({errCode:0,message:'删除商品类型成功'});
        })
    })
});

//查商品列表
router.get('/list',function(req,res) {
    let countPageSql = "select count(`id`) as total from `product` where `upper_shelf`=1";
    let page = req.query.page;
    let option = "select p.*,t.`name` as `type_name` from `product` as p join `product_type` as t on p.`type_id`=t.`id` where `upper_shelf`=1 limit ";
    pagination(countPageSql,page,option,function (result) {
        return res.send(result)
    });
});
//增商品
router.post('/add', function (req, res) {
    let pic = req.body.bannerPic;
    let name = req.body.name;
    let disc = req.body.disc;
    let principal = req.body.principal;
    let price = req.body.price;
    let detail = req.body.detailPic;
    let type_id = req.body.type;
    let upper_shelf = req.body.upper_shelf;
    let option = "insert into `product`(`pic`,`name`,`disc`,`principal`,`price`,`detail`,`type_id`,`upper_shelf`) values('" + pic + "','" + name +"','" + disc + "','" +principal + "','" +price + "','" +detail + "','" + type_id + "','" + upper_shelf +"')";
    sql.query(option, function (err, rows) {
        if (err)
            return res.send({errCode:-9999,message:'新增商品失败'+err});
        res.send({errCode:0,message:'新增商品成功'});
    })
});
// 改商品属性
router.post('/alter', function (req, res) {
    let id = req.body.id;
    let pic = req.body.bannerPic;
    let name = req.body.name;
    let disc = req.body.disc;
    let principal = req.body.principal;
    let price = req.body.price;
    let detail = req.body.detailPic;
    let type_id = req.body.type;
    let option = "update `product` set `pic`='" + pic + "',`name`='" + name + "',`disc`='" + disc +"',`principal`='" + principal +"',`price`='" + price +"',`detail`='" + detail +"',`type_id`='" + type_id + "' where `id`=" + id;
    sql.query(option, function (err, rows) {
        if (err)
            return res.send({errCode:-9999,message:'修改商品失败'+err});
        res.send({errCode:0,message:'修改商品类型成功'});
    })
});
//删商品
router.post('/remove', function (req, res) {
    let id = req.body.id;
    let option = "UPDATE `product` SET `upper_shelf` = 0 WHERE `id` = "+id;
    sql.query(option, function (err, rows) {
        if (err)
           return res.send({errCode:-9999,message:'操作数据库失败'+err});
        res.send({errCode:0,message:"该商品已下架"});
    })
});
//删商品多条记录
router.post('/removeMore', function (req, res) {
    let idList = req.body;
    let str = "";
    idList.forEach((val,index,arr)=>{
        str+=val+","
    });
    str = str.substring(0,str.length - 1);
    let option = "UPDATE `product` SET `upper_shelf` = 0 WHERE `id` in ("+str+")";
    sql.query(option, function (err, rows) {
        if (err)
            return res.send({errCode:-9999,message:'操作数据库失败'+err});
        res.send({errCode:0,message:"商品已批量下架"});
    })
});
//根据商品名称搜索
router.get('/search', function (req, res, next) {
    let key = req.query.key;
    let countPageSql = "select count(`id`) as total from `product` where `upper_shelf`=1 and `name` like '%"+key+"%'";
    let page = req.query.page;
    let option = "select p.*,t.`name` as `type_name` from `product` as p join `product_type` as t on p.`type_id`=t.`id` where p.`upper_shelf`=1 and p.`name` like '%"+key+"%' limit ";
    pagination(countPageSql,page,option,function (result) {
        return res.send(result)
    });
});

//商品排行榜
router.get('/popular', function (req, res, next) {
    let page = req.query.page;
    let countSql = "select `product_id` from `order` where `order_payment_status`=1 group by `product_id`";
    let num = 8; //一页条数
    let totalPage = null;//总页数
    sql.query(countSql, function (err, rows) {
        if(err) return res.send({errCode:-9999,message:'查询失败'+err});
        if(rows.length==0) return res.send({errCode:9,message:'暂无数据'});
        totalPage = Math.ceil(rows.length/num);
        let current_page = 1; //默认为1
        if (page) current_page = parseInt(page);
        let last_page = current_page - 1;
        if (current_page <= 1) last_page = 1;
        let next_page = current_page + 1;
        let option = "select `product_id`,sum(`num`) as total from `order` where `order_payment_status`=1 group by `product_id` limit "+num+" offset "+num*(current_page-1);
        sql.query(option, function (err, rows) {
            if (err) return res.send({errCode: -9999, message: '查询失败' + err});
            if (!rows[0]) return res.send({errCode: 9, message: '已是最后一页了喔'});
            let quantityResult = rows;//product_id+total
            let productIdList = quantityResult.map((val, index, arr) => {
                return val.product_id
            });//product_id
            let getProductSql = "select p.`id`, p.`name`, p.`price`, t.`name` as `type_name` from `product` as p join `product_type` as t on p.`type_id`=t.`id` where p.`id` in (" + [...productIdList] + ")";
            sql.query(getProductSql, function (err, rows) {
                if (err) return res.send({errCode: -9999, message: '操作数据库失败' + err});
                let result = rows.map((product, index, arr) => {
                    //product
                    quantityResult.forEach((val, index, arr) => {
                        //product_id+total
                        if (val.product_id == product.id)
                            product.total = val.total;
                    });
                    return product;
                });
                function compare(property){
                    return function(a,b){
                        let value1 = a[property];
                        let value2 = b[property];
                        return value2 - value1;
                    }
                }
                result.sort(compare('total'));
                let data = {
                    total_page: totalPage,
                    last_page: last_page,
                    next_page: next_page,
                    current_page: current_page,
                    list: result
                };
                res.send({errCode: 0, message: "查询成功", result: data});
            })
        })
    });
});

//商品排行榜搜索
router.get('/popular/search', function (req, res, next) {
    let key = req.query.key;
    let page = req.query.page;
    let countSql = "select count(o.`product_id`) as total from `order` as o, `product` as p where o.`order_payment_status`=1 and p.`id`=o.`product_id` and p.`name` like '%"+key+"%' group by o.`product_id`";
    let optionSql = "select o.`product_id`, sum(o.`num`) as total, p.`name`, p.`price`, t.`name` as type_name from `order` as o,`product` as p, `product_type` as t where o.`order_payment_status`=1 and p.`id`=o.`product_id` and t.`id`=p.`type_id` and p.`name` like '%"+key+"%' group by o.`product_id` limit ";
    let num = 8; //一页条数
    let totalPage = null;//总页数
    sql.query(countSql, function (err, rows) {
        if (err) return res.send({errCode:-9999,message:'查询失败'+err});
        if(rows.length==0) return res.send({errCode:9,message:'商品没有被下过单啦！'});
        totalPage = Math.ceil(rows.length/num);
        let current_page = 1; //默认为1
        if (page) current_page = parseInt(page);
        let last_page = current_page - 1;
        if (current_page <= 1) last_page = 1;
        let next_page = current_page + 1;
        let option = optionSql+num+" offset "+num*(current_page-1);
        sql.query(option, function (err, rows) {
            if (err) return res.send({errCode:-9999,message:'查询失败'+err});
            if (!rows[0]) return res.send({errCode:9,message:'已是最后一页了喔'});
            let data = {
                total_page:totalPage,
                last_page:last_page,
                next_page: next_page,
                current_page: current_page,
                result:rows
            };
            return res.send({errCode:0,message:'查询成功',data:data});
        })
    });
});

module.exports = router;


